package base;

/**
 * Created by Developer on 10.06.13.
 */

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

import com.example.costs.ParentLV;

public class DB {
    private static final String DB_NAME       = "costs";
    private static final int    DB_VERSION    = 1;
    private static final String TRANSACTIONS  = "transactions";
    private static final String CATEGORY      = "category";
    private static final String USERS         = "users";



    //----- Table TRANSACTIONS fields-----
    public static final String TR_ID          = "tr_id";
    public static final String TR_USERID      = "tr_userid";
    public static final String TR_DATE        = "tr_date";
    public static final String TR_AMOUNT      = "tr_amount";
    public static final String TR_CTGID       = "tr_ctgid";
    public static final String TR_TYPE        = "tr_type";
    public static final String TR_DESRIPTION  = "tr_description";

    //----- Table CATEGORY fields-----
    public static final String CTG_ID         = "ctg_id";
    public static final String CTG_USERID     = "ctg_userid";
    public static final String CTG_NAME       = "ctg_name";
    public static final String CTG_TYPE       = "ctg_type";

    //----- Table USERS fields-----
    public static final String USR_ID         = "usr_id";
    public static final String USR_LOGIN      = "usr_login";
    public static final String USR_PASSWORD   = "usr_password";
    public static final String USR_NAME       = "usr_name";

    private static final String CREATE_TRANSACTIONS =
            "create table " + TRANSACTIONS + "("
                    + TR_ID          + " integer primary key autoincrement, "
                    + TR_USERID      + " integer, "
                    + TR_DATE        + " datetime , "
                    + TR_AMOUNT      + " decimal(10,2), "
                    + TR_CTGID       + " integer, "
                    + TR_TYPE        + " text, "
                    + TR_DESRIPTION  + " text"
                    + ");";

    private static final String CREATE_CATEGORY =
            "create table " + CATEGORY + "("
                    + CTG_ID         + " integer primary key autoincrement, "
                    + CTG_USERID     + " integer, "
                    + CTG_NAME       + " text, "
                    + CTG_TYPE       + " text "
                    + ");";

    private static final String CREATE_USERS =
            "create table " + USERS + "("
                    + USR_ID         + " integer primary key autoincrement, "
                    + USR_LOGIN      + " text, "
                    + USR_PASSWORD   + " text, "
                    + USR_NAME       + " text  "
                    + ");";

    private final Context mCtx;

    private DBHelper mDBHelper;
    private SQLiteDatabase mDB;

    public DB(Context ctx) {
        mCtx = ctx;
    }

    public void open() {
        mDBHelper = new DBHelper(mCtx, DB_NAME, null, DB_VERSION);
        mDB = mDBHelper.getWritableDatabase();
    }

    public void close() {
        if (mDBHelper != null)
            mDBHelper.close();
    }

    //    Для курсора
    public Cursor getRefills() {
        return mDB.query(TRANSACTIONS, null, null, null, null, null, TR_ID+ " DESC");
    }

    public Integer getIntegerByQuery(String _sqlQuery) {
        Cursor c = mDB.rawQuery(_sqlQuery, null);

        if (c != null && c.moveToFirst())
            return c.getInt(0);
        return null;
    }

    public BigDecimal getBigDecimalByQuery(String _sqlQuery) {
        Cursor c = mDB.rawQuery(_sqlQuery, null);
        if (c != null && c.moveToFirst() && c.getString(0)!=null)
            return new BigDecimal(c.getString(0));
        return BigDecimal.ZERO;
    }

    public String getStringByQuery(String _sqlQuery) {
        Cursor c = mDB.rawQuery(_sqlQuery, null);
        if (c != null && c.moveToFirst())
            return c.getString(0);
        return null;
    }

    public Cursor getCursorByQuery(String _sqlQuery) {
        return mDB.rawQuery(_sqlQuery, null);
    }

    public void addTrans(Integer uId, String date, double amount, Integer ctgId, String type, String descriprion) {
        ContentValues cv = new ContentValues();
        cv.put(TR_USERID,      uId);
        cv.put(TR_DATE,        date);
        cv.put(TR_AMOUNT,      amount);
        cv.put(TR_CTGID,       ctgId);
        cv.put(TR_TYPE,        type);
        cv.put(TR_DESRIPTION,  descriprion);
        mDB.insert(TRANSACTIONS, null, cv);
    }

    public Integer getCtgIdByName(Integer uId, String Name){
        String sql = "select "+CTG_ID+" from "+CATEGORY+" where "+CTG_USERID+"="+uId+" and "+CTG_NAME+"='"+Name+"'";
        Lg.sql(sql);
        return  getIntegerByQuery(sql);
    }

    public Category getCtgById(Integer uId, Integer CtgId){
        String sql = "select * from "+CATEGORY+" where "+CTG_ID+"="+CtgId+" and "+CTG_USERID+"='"+uId+"'";
        Lg.sql(sql);
        Cursor c = getCursorByQuery(sql);
        if (c != null) {
            if (c.moveToFirst()) {
                return new Category(c.getInt(c.getColumnIndex(CTG_USERID)),c.getInt(c.getColumnIndex(CTG_ID)),c.getString(c.getColumnIndex(CTG_NAME)),c.getString(c.getColumnIndex(CTG_TYPE)));
            }
            c.close();
        } else
            Lg.sqlMes("Cursor is null");

        return  null;
    }

    public Integer addCtg(Integer uId, String Name, String type){
        ContentValues cv = new ContentValues();
        cv.put(CTG_USERID,      uId);
        cv.put(CTG_NAME,        Name);
        cv.put(CTG_TYPE,        type);
        Long id = mDB.insert(CATEGORY, null, cv);
        if(id == -1){
            new Exception("error insert in method addCtg");
        }
        return id.intValue();
    }

//    public void delRefill(String _id) {
//        mDB.delete(REFILLS, ID + " = " + _id, null);
//    }

    public Cursor fetchAllCtg(int uid, String type){
        StringBuilder sql = new StringBuilder("select ");
        sql.append(CTG_NAME);sql.append(" from ");sql.append(CATEGORY);sql.append(" where ");sql.append(CTG_USERID);sql.append(" = ");sql.append(uid);sql.append(" and ");sql.append(CTG_TYPE);sql.append(" = '");sql.append(type);sql.append("'");
        Lg.sql(sql.toString());
        return getCursorByQuery(sql.toString());
    }

    public ArrayList fetchAllTr(int uid, String type){
        String sql = "select tr."+TR_DATE+", ctg."+CTG_NAME+", tr."+TR_AMOUNT+" from "+TRANSACTIONS+" as tr, "+CATEGORY+" as ctg where tr."+TR_CTGID+" = ctg."+CTG_ID+" and tr."+TR_USERID+" = "+uid+" and tr."+TR_TYPE+" = '"+type+"'";
        Lg.sql(sql);
        Cursor cursor = getCursorByQuery(sql);
        ArrayList<Map<String, Object>> arrayTr = new ArrayList();
        Map<String, Object> tr;


        if (cursor != null) {
            while(cursor.moveToNext()) {
                tr = new HashMap<String, Object>();
                tr.put(TR_DATE,cursor.getString(0));
                tr.put(CTG_NAME,cursor.getString(1));
                arrayTr.add(tr);
            }
            cursor.close();
        }
        return arrayTr;
    }

    public ArrayList<ParentLV> fetchAllTrParent(int uid, String type){
        String sql = "select tr."+TR_DATE+", ctg."+CTG_NAME+", tr."+TR_AMOUNT+" from "+TRANSACTIONS+" as tr, "+CATEGORY+" as ctg where tr."+TR_CTGID+" = ctg."+CTG_ID+" and tr."+TR_USERID+" = "+uid+" and tr."+TR_TYPE+" = '"+type+"'";
        Lg.sql(sql);
        Cursor cursor = getCursorByQuery(sql);
        ArrayList<ParentLV> arrayParents = new ArrayList<ParentLV>();
        ArrayList arrayChildren = new ArrayList<String>();
        ArrayList<String> arrayChildrenFields = new ArrayList<String>();
        String dateBefore = null;
        ParentLV parent = new ParentLV();
        int cursorLen = cursor.getCount();
        if (cursor != null) {
            while(cursor.moveToNext()) {
                arrayChildrenFields = new ArrayList<String>();
                for (int i = 1; i < cursor.getColumnCount(); i++) {
                    arrayChildrenFields.add(cursor.getString(i));
                }
                arrayChildren.add(arrayChildrenFields);
                if(parent == null){
                    parent = new ParentLV();
                    parent.setArrayChildren(arrayChildren);
                    arrayParents.add(parent);

                }
                if((dateBefore!=null && !dateBefore.equals(cursor.getString(0)))){
                    parent.setArrayChildren(arrayChildren);
                    parent.setTitle(dateBefore);
                    arrayParents.add(parent);
                    parent = null;
                    arrayChildren = new ArrayList<String>();

                } else if(cursor.isLast()){
                    parent.setArrayChildren(arrayChildren);
                    parent.setTitle(cursor.getString(0));
                    arrayParents.add(parent);
                    parent = null;
                    arrayChildren = new ArrayList<String>();
                }
                dateBefore = cursor.getString(0);
            }
            cursor.close();
        }
        return arrayParents;
    }

    public void execSQL(String _querry){
        mDB.execSQL(_querry);
    }

    private class DBHelper extends SQLiteOpenHelper {

        public DBHelper(Context context, String name, CursorFactory factory, int version) {
            super(context, name, factory, version);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
//      db.execSQL("drop table " + TAB_DATA);
            db.execSQL(CREATE_CATEGORY);
            db.execSQL(CREATE_TRANSACTIONS);
            db.execSQL(CREATE_USERS);
        }

        @Override
        public void onOpen(SQLiteDatabase db) {

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        }
    }

}